package Aug28;

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


class DBManager{
	public static DBManager instance = new DBManager();
	public static final String URL = "jdbc:mysql://localhost/Practice?";
	public static final String USER = "root";
	public static final String PASSWORD = "root";
	public static final String DRIVER_CLASS = "com.mysql.jdbc.Driver";
	
	private static Connection conn;		//why 'conn' cannot be private inside a method and has to be static if declared at class level
	private static Statement stmt;			//why 'stmt' cannot be private inside a method and has to be static if declared at class level
	
	private DBManager(){
		try{
			Class.forName(DRIVER_CLASS);
		}
		catch(ClassNotFoundException cnfe){
			System.out.println("Class Not Found");
		}
	}
	
	private Connection createConnection(){
		Connection conn= null;
		try{
			conn = DriverManager.getConnection(URL,USER,PASSWORD);
		}
		catch(SQLException se){
			System.out.println("Unable to connect to database");
		}
		return conn;
	}
	
	public static Connection getConnection(){
		return instance.createConnection();
	}
	
	public static void insert(String name, String email){
		boolean value = false;
		try{
			conn = DBManager.getConnection();
			stmt = conn.createStatement();
			String query = "INSERT INTO User (Name, Email) VALUES(\"" + name + "\", \"" + email +"\");";
			System.out.println(query);
			value = stmt.execute(query);
			
		}
		catch(SQLException se){
			System.out.println("Error!!!" + se.toString());
		}
		if(value)
			System.out.println("Data Successfully Added");
		else
			System.out.println("Data Addtion Unsuccessful");
	}
	
	public static void update(){
		int value = 0;
		try{
			conn = DBManager.getConnection();
			stmt = conn.createStatement();
			value = stmt.executeUpdate("UPDATE User SET id = id+1000");
		}
		catch(SQLException se){
			se.printStackTrace();
		}
		if(value!=0)
			System.out.println("Update Successful");
		else
			System.out.println("Update Unsuccessful");
	}
	
	public static void delete(int i){
		int value = 0;
		try{
			conn = DBManager.getConnection();
			stmt = conn.createStatement();
			value = stmt.executeUpdate("DELETE FROM User WHERE id = "+i);
		}
		catch(SQLException se){
			se.printStackTrace();
		}
		if(value !=0)
			System.out.println("Row Successfully Deleted");
		else
			System.out.println("Deletion Unsuccessful");
	}
	
	public static ResultSet retrieve(int i){
		ResultSet rs = null;
		try{
			conn = DBManager.getConnection();
			stmt = conn.createStatement();
			String query = "SELECT * FROM USER WHERE id = " + i + ";";
			System.out.println(query);
			rs = stmt.executeQuery(query);
			System.out.println("Name: " + rs.getString("Name"));
			System.out.println("Email: " + rs.getString("Email"));
		}
		catch(SQLException se){
			se.printStackTrace();
		}
		return rs;
	}
	

}

 public class Test {
	
	private int id;
	String name;
	String email;
	
	Test(){
		
	}
		
	Test(int i, String n, String e){
		
		this.id = i;
		this.name = n;
		this.email = e;
	}
	
	public static void saveUser(Test u){
		DBManager.insert(u.name, u.email);
	}
	
	public static void main(String[] args){
		Test u = new Test();
		u.name = "Nischal Nayak";
		u.email = "nischal.nayak@facebook.com";
		Test.saveUser(u);
	}
}